How to drop a user in SQL Server?

Question

Ashutosh Kumar Verma · Answer

SQL Server DROP USER
The SQL statement DROP USER allows you to delete a user from an existing database. Here is the syntax of the
DROP USER statement
DROP USER [IF EXISTS] user_name;
In the syntax above, don't forget to replace your use name with the user_name after the keywork
DROP USER. If the user doesn't exist in the current database then 
DROP USER statement will fail.
To avoid this, you can use the IF EXISTS option. If the user already exists, the
IF EXISTS option conditionally deletes them.
and 
1. Using the DROP USER statement to delete a user
Now, use the MyCollegeDb sample database for the following 
DROP USER statement example.
First, create a new login testLogin with a password,
CREATE LOGIN testLogin
WITH PASSWORD='UyxIv@12'
Second, 
create a new user and map it with the login testLoginand 
USE MyCollegeDb
GO
CREATE USER MyUser
FOR LOGIN testLogin;
Third, drop the user MyUser from the current database
USE MyCollegeDb
GO
DROP USER IF EXISTS MyUser;
and 
2. Drop a user that owns a securable
First, create a new login called jacob with a password,
USE master
GO
CREATE LOGIN jacob
WITH PASSWORD = 'Jac.B908$#'
Second, use your current database MyCollegeDb and create a new user for the login
jacob
USE MyCollegeDb
GO
CREATE USER ami
FOR LOGIN jacob;
Third, create a schema called report and grant authorization to the user
ami.
USE MyCollegeDb
GO
CREATE SCHEMA report 
AUTHORIZATION ami;
and 
Fourth, connect to the SQL Server using the login jacob and create a table called
daily_sales in the schema report
USE MyCollegeDb
GO
CREATE TABLE report.daily_sales (
Id INT IDENTITY PRIMARY KEY,
CreationDate DATETIME NOT NULL,
Amount DECIMAL(10,2) NOT NULL DEFAULT 0
)
Fifth, switch the connection to the system administrator (sa) account and drop the user
ami
USE MyCollegeDb
GO
DROP USER ami;
The SQL server Returns the following error,
Because the user ami owns the schema report, the 
DROP USER statement cannot delete it.
To delete user ami, you must first transfer the authorization of the schema report to another user. For example, the following statement changes the authorization of the schema
report to user dbo:
USE MyCollegeDb
GO
ALTER AUTHORIZATION 
ON SCHEMA::report 
TO dbo;
If you execute the DROP USER statement to delete the user ami, it will executes successfully,
USE MyCollegeDb
GO
DROP User ami;
and 
Also, Read: 
Describe the different types roles in SQL Server.

forum

How to drop a user in SQL Server?

Sandra Emily

Can you answer this question?

1 Answers

SQL Server DROP USER

1. Using the DROP USER statement to delete a user

2. Drop a user that owns a securable

Liked By